/**
 * Copyright 2011 HNB. All rights reserved.
 * HNB PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
 */
package my.hnb.android.view.karaoke;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;

import my.hnb.android.common.AppInfo;
import my.hnb.android.dto.SongDTO;
import my.hnb.android.dto.SongTable;
import my.hnb.android.model.SQLUtils;
import my.hnb.android.ultils.StringUtil;
import my.hnb.android.view.base.BaseActivity;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import android.app.Dialog;
import android.app.ProgressDialog;
import android.content.ContentValues;
import android.content.Context;
import android.content.res.AssetManager;
import android.os.AsyncTask;
import android.util.Log;

/**
 * Class descriptions
 * 
 * @author BANGHN
 */
public class ImportDataManager extends BaseActivity {
	public final static String TAG = ImportDataManager.class.getName();
	private static ImportDataManager instance = null;
	private static final Object lockObject = new Object();
	
	public static final int DIALOG_DOWNLOAD_PROGRESS = 0;
    private ProgressDialog mProgressDialog;
    
	public static ImportDataManager getInstance() {
		if (instance == null) {
			synchronized (lockObject) {
				if (instance == null) {
					instance = new ImportDataManager();
				}
			}
		}
		return instance;
	}
	
	@Override
	protected Dialog onCreateDialog(int id) {
		switch (id) {
        case DIALOG_DOWNLOAD_PROGRESS:
            mProgressDialog = new ProgressDialog(AppInfo.getInstance().getActivityContext());
            mProgressDialog.setMessage("Cập nhật bài hát..");
            mProgressDialog.setProgressStyle(ProgressDialog.STYLE_HORIZONTAL);
            mProgressDialog.setCancelable(false);
            mProgressDialog.show();
            return mProgressDialog;
        default:
            return null;
        }
	}

	public void generateShortTitle() {
		ArrayList<SongDTO> model = SQLUtils.getInstance().getTileOfSong();
		int size = model.size();
		for (int i = 0; i < size; i++) {
			SQLUtils.getInstance().updateSongTile(model.get(i).songNumber,
					StringUtil.getFristCharacterOfLine(model.get(i).songName));
			Log.d("generateShortTitle", "...... " + i);
		}
	}
	
	
	public void executeUpdateData(){
		new UpdateDataAsync().execute("");
	}
	
	
	class UpdateDataAsync extends AsyncTask<String, String, String> {

		@Override
		protected void onPreExecute() {
			showDialog(DIALOG_DOWNLOAD_PROGRESS);
			super.onPreExecute();
		}
		
		@Override
		protected String doInBackground(String... arg0) {
			try {
				AssetManager assetManager = AppInfo.getInstance().getAppContext().getAssets();
				InputStream in = assetManager.open("Karaoke_Template.xls");

				// Create a POIFSFileSystem object
				POIFSFileSystem myFileSystem = new POIFSFileSystem(in);

				// Create a workbook using the File System
				HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);

				// Get the first sheet from workbook
				HSSFSheet mySheet = myWorkBook.getSheetAt(0);
				Iterator<?> rowIter = mySheet.rowIterator();
				int totalRow = mySheet.getPhysicalNumberOfRows();
				
				int i = 0;
				ArrayList<String> columns = new ArrayList<String>();
				ArrayList<String> values;
				while (rowIter.hasNext()) {
					HSSFRow myRow = (HSSFRow) rowIter.next();
					Iterator<?> cellIter = myRow.cellIterator();
					values = new ArrayList<String>();
					while (cellIter.hasNext()) {
						HSSFCell myCell = (HSSFCell) cellIter.next();
						if(i == 0){
							columns.add(myCell.toString());
						}else{
							values.add(myCell.toString());
						}
						Log.d(TAG, "Cell Value: " + myCell.toString());
					}
					if(i != 0 && StringUtil.checkIfNumber(values.get(0).replace(".0", ""))){
						ContentValues content = initDataRow(columns, values);
						String[] params = { values.get(0).replace(".0", "") };
						if(SQLUtils.getInstance().isExistSong(values.get(0).replace(".0", ""))){
							//update
							long id = SQLUtils.getInstance().getmDB().update("SONG", content, "SONG_CODE = ?", params);
							Log.d(TAG, "Row Value: " + id );
						}else{
							//insert
							long id =  SQLUtils.getInstance().getmDB().insert("SONG", null, content);
							Log.d(TAG, "Row Value: " + id );
						}
					}
					i++;
					publishProgress(""+(int)((i*100)/totalRow));
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
			return null;
		}
		
		
		protected void onProgressUpdate(String... progress) {
	         mProgressDialog.setProgress(Integer.parseInt(progress[0]));
	    }

	    @Override
	    protected void onPostExecute(String unused) {
	        dismissDialog(DIALOG_DOWNLOAD_PROGRESS);
	    }
	}
	
	
	
	public void readExcelFile(Context context) {

		// if (!isExternalStorageAvailable() || isExternalStorageReadOnly())
		// {
		// Log.e(TAG, "Storage not available or read only");
		// return;
		// }

		try {
			AssetManager assetManager = AppInfo.getInstance().getAppContext().getAssets();
			InputStream in = assetManager.open("Karaoke_Template.xls");

			 //Creating Input Stream
//			 File file = new File(context.getExternalFilesDir(null),
//			 filename);
//			 FileInputStream myInput = (FileInputStream) in;

			// Create a POIFSFileSystem object
			POIFSFileSystem myFileSystem = new POIFSFileSystem(in);

			// Create a workbook using the File System
			HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);

			// Get the first sheet from workbook
			HSSFSheet mySheet = myWorkBook.getSheetAt(0);
			Iterator<?> rowIter = mySheet.rowIterator();
			
			int i = 0;
			ArrayList<String> columns = new ArrayList<String>();
			ArrayList<String> values;
			while (rowIter.hasNext()) {
				HSSFRow myRow = (HSSFRow) rowIter.next();
				Iterator<?> cellIter = myRow.cellIterator();
				values = new ArrayList<String>();
				while (cellIter.hasNext()) {
					HSSFCell myCell = (HSSFCell) cellIter.next();
					if(i == 0){
						columns.add(myCell.toString());
					}else{
						values.add(myCell.toString());
					}
					Log.d(TAG, "Cell Value: " + myCell.toString());
				}
				if(i != 0 && StringUtil.checkIfNumber(values.get(0).replace(".0", ""))){
					ContentValues content = initDataRow(columns, values);
					String[] params = { values.get(0).replace(".0", "") };
					if(SQLUtils.getInstance().isExistSong(values.get(0).replace(".0", ""))){
						//update
						long id = SQLUtils.getInstance().getmDB().update("SONG", content, "SONG_CODE = ?", params);
						Log.d(TAG, "Row Value: " + id );
					}else{
						//insert
						long id =  SQLUtils.getInstance().getmDB().insert("SONG", null, content);
						Log.d(TAG, "Row Value: " + id );
					}
				}
				i++;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return;
	}

	
	private ContentValues initDataRow(ArrayList<String> columns, ArrayList<String> values) {
		ContentValues editedValues = new ContentValues();
		try {

			editedValues.put(SongTable.COLUMN_SONG_CODE, values.get(0).replace(".0", ""));
			editedValues.put(SongTable.COLUMN_SONG_NAME, values.get(1));
			editedValues.put(SongTable.COLUMN_MUSICAN, values.get(2));
			editedValues.put(SongTable.COLUMN_SINGER, values.get(3));
			editedValues.put(SongTable.COLUMN_VOL, values.get(4).replace(".0", ""));
			editedValues.put(SongTable.COLUMN_TYPE, values.get(5).replace(".0", ""));
			editedValues.put(SongTable.COLUMN_LYRIC_SHORT, values.get(6));
			editedValues.put(SongTable.COLUMN_LYRIC_FULL, values.get(7));
			
			editedValues.put(SongTable.COLUMN_SONG_NAME_SEARCH, StringUtil.getEngStringFromUnicodeString(values.get(1)).toUpperCase());
			editedValues.put(SongTable.COLUMN_MUSICAN_SEARCH, StringUtil.getEngStringFromUnicodeString(values.get(2)).toUpperCase());
			editedValues.put(SongTable.COLUMN_LYRIC_SEARCH, StringUtil.getEngStringFromUnicodeString(values.get(6)).toUpperCase());
			editedValues.put(SongTable.COLUMN_SONG_NAME_SHORT, StringUtil.getFristCharacterOfLine(values.get(1)).toUpperCase());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return editedValues;
	}
}
